﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using UniMana.Data;



namespace UniMana.Data
{
    public static class ReportStudent_AddressData_Chi
    {
        /// <summary>
        /// thống kê địa chỉ sinh viên theo khóa và ngành
        /// </summary>
        /// <param name="cataCity"></param>
        /// <param name="cataCourse"></param>
        /// <param name="student_Class"></param>
        /// <param name="strClass"></param>
        /// <param name="cataMajor"></param>
        /// <param name="student"></param>
        /// <param name="_iCourse"></param>
        /// <param name="_iMajor"></param>
        /// <returns></returns>
        public static IQueryable Student_City(this Table<Catalogue_City> cataCity,
                       Table<Catalogue_Course> cataCourse,
                       Table<Training_Student_Class> student_Class,
                       Table<Training_Class> strClass,
                       Table<Catalogue_Major> cataMajor,
                      Table<Training_Student> student, int _iCourse, int _iMajor)
        {
            var view = from t in student
                       join t0 in cataCity on new { Catalogue_CityKey = Convert.ToInt32(t.Catalogue_CityKey) } equals new { Catalogue_CityKey = t0.Catalogue_CityKey }
                       join t1 in student_Class on t.Training_StudentKey equals t1.Training_StudentKey
                       join t2 in cataCourse on new { Catalogue_CourseKey = Convert.ToInt32(t1.Catalogue_CourseKey) } equals new { Catalogue_CourseKey = t2.Catalogue_CourseKey }
                       join t3 in cataMajor on t1.Catalogue_MajorKey equals t3.Catalogue_MajorKey
                       join t4 in strClass on new { Training_ClassKey = Convert.ToInt32(t1.Training_ClassKey) } equals new { Training_ClassKey = t4.Training_ClassKey }
                       group new { t2, t0, t3, t4 } by new
                       {
                           t2.Catalogue_CourseKey,
                           t2.Name,
                           t0.Catalogue_CityKey,
                           Column1 = t0.Name,
                           t3.Catalogue_MajorKey,
                           t3.ID,
                           Column2 = t3.Name,
                           Column3 = t4.ID,
                           t4.Training_ClassKey,
                           Column4 = t4.Name
                       } into g
                       where g.Key.Catalogue_CourseKey == _iCourse &&
                         g.Key.Catalogue_MajorKey == _iMajor
                       orderby
                         g.Key.Name
                       select new
                       {
                           Catalogue_CourseKey = (System.Int32?)g.Key.Catalogue_CourseKey,
                           TenKhoaHoc = g.Key.Name,
                           Catalogue_CityKey = (System.Int32?)g.Key.Catalogue_CityKey,
                           TenTinhThanh = g.Key.Column1,
                           TongSoSV = (Int64?)g.Count(),
                           Catalogue_MajorKey = (System.Int32?)g.Key.Catalogue_MajorKey,
                           g.Key.ID,
                           TenNganh = g.Key.Column2,
                           MaLop = g.Key.Column3,
                           Training_ClassKey = (System.Int32?)g.Key.Training_ClassKey,
                           TenLopDV = g.Key.Column4
                       };
            return view;
        }
        /// <summary>
        /// thống kê địa chỉ sinh viên theo ngành
        /// </summary>
        /// <param name="cataCity"></param>
        /// <param name="cataCourse"></param>
        /// <param name="student_Class"></param>
        /// <param name="strClass"></param>
        /// <param name="cataMajor"></param>
        /// <param name="student"></param>
        /// <param name="_iCourse"></param>
        /// <returns></returns>
        public static IQueryable Student_City_Major(this Table<Catalogue_City> cataCity,
                       Table<Catalogue_Course> cataCourse,
                       Table<Training_Student_Class> student_Class,
                       Table<Training_Class> strClass,
                       Table<Catalogue_Major> cataMajor,
                      Table<Training_Student> student, int _iMaj)
        {
            var view2 = from t in student
                        join t0 in cataCity on new { Catalogue_CityKey = Convert.ToInt32(t.Catalogue_CityKey) } equals new { Catalogue_CityKey = t0.Catalogue_CityKey }
                        join t1 in student_Class on t.Training_StudentKey equals t1.Training_StudentKey
                        join t2 in cataCourse on new { Catalogue_CourseKey = Convert.ToInt32(t1.Catalogue_CourseKey) } equals new { Catalogue_CourseKey = t2.Catalogue_CourseKey }
                        join t3 in cataMajor on t1.Catalogue_MajorKey equals t3.Catalogue_MajorKey
                        join t4 in strClass on new { Training_ClassKey = Convert.ToInt32(t1.Training_ClassKey) } equals new { Training_ClassKey = t4.Training_ClassKey }
                        group new { t2, t0, t3, t4 } by new
                        {
                            t2.Catalogue_CourseKey,
                            t2.Name,
                            t0.Catalogue_CityKey,
                            Column1 = t0.Name,
                            t3.Catalogue_MajorKey,
                            Column2 = t3.Name,
                            t4.Training_ClassKey,
                            Column3 = t4.Name
                        } into g
                        where g.Key.Catalogue_MajorKey == _iMaj
                        orderby
                          g.Key.Name
                        select new
                        {
                            Catalogue_CourseKey = (System.Int32?)g.Key.Catalogue_CourseKey,
                            TenKhoaHoc = g.Key.Name,
                            Catalogue_MajorKey = (System.Int32?)g.Key.Catalogue_MajorKey,
                            TenNganh = g.Key.Column2,
                            Training_ClassKey = (System.Int32?)g.Key.Training_ClassKey,
                            TenLopDV = g.Key.Column3,
                            Catalogue_CityKey = (System.Int32?)g.Key.Catalogue_CityKey,
                            TenTinhThanh = g.Key.Column1,
                            TongSoSV = (Int64?)g.Count()
                        };
                    return view2;
        }

        /// <summary>
        /// Thống kê địa chỉ sv theo khóa
        /// </summary>
        /// <param name="cataCity"></param>
        /// <param name="cataCourse"></param>
        /// <param name="student_Class"></param>
        /// <param name="strClass"></param>
        /// <param name="cataMajor"></param>
        /// <param name="student"></param>
        /// <param name="_iCourse"></param>
        /// <returns></returns>
        public static IQueryable Student_City_Course(this Table<Catalogue_City> cataCity,
                       Table<Catalogue_Course> cataCourse,
                       Table<Training_Student_Class> student_Class,
                       Table<Training_Class> strClass,
                       Table<Catalogue_Major> cataMajor,
                       Table<Training_Student> student, int _iCourse)
        {
            var view3 = from t in student
                        join t0 in cataCity on new { Catalogue_CityKey = Convert.ToInt32(t.Catalogue_CityKey) } equals new { Catalogue_CityKey = t0.Catalogue_CityKey }
                        join t1 in student_Class on t.Training_StudentKey equals t1.Training_StudentKey
                        join t2 in cataCourse on new { Catalogue_CourseKey = Convert.ToInt32(t1.Catalogue_CourseKey) } equals new { Catalogue_CourseKey = t2.Catalogue_CourseKey }
                        join t3 in cataMajor on t1.Catalogue_MajorKey equals t3.Catalogue_MajorKey
                        join t4 in strClass on new { Training_ClassKey = Convert.ToInt32(t1.Training_ClassKey) } equals new { Training_ClassKey = t4.Training_ClassKey }
                        group new { t0, t3, t4, t1, t2 } by new
                        {
                            t0.Catalogue_CityKey,
                            t0.Name,
                            t3.Catalogue_MajorKey,
                            Column1 = t3.Name,
                            Column2 = t4.Name,
                            t1.Training_ClassKey,
                            t2.Catalogue_CourseKey,
                            Column3 = t2.Name
                        } into g
                        where g.Key.Catalogue_CourseKey == _iCourse
                        select new
                        {
                            Catalogue_CourseKey = (System.Int32?)g.Key.Catalogue_CourseKey,
                            TenKhoaHoc = g.Key.Column3,
                            Catalogue_MajorKey = (System.Int32?)g.Key.Catalogue_MajorKey,
                            TenNganh = g.Key.Column1,
                            Training_ClassKey = (System.Int32?)g.Key.Training_ClassKey,
                            TenLopDV = g.Key.Column2,
                            Catalogue_CityKey = (System.Int32?)g.Key.Catalogue_CityKey,
                            TenTinhThanh = g.Key.Name,
                            TongSoSV = (Int64?)g.Count()
                        };
            return view3;
        }
    }
}
  